<?php
class CompaniesModel extends BaseModel implements Model {
    function __construct() {
        parent::__construct("companies", "company_id");
        $this->addField("company_id", Model::TYPE_INT, Model::MOD_PRIMARY);
        $this->addField("country", Model::TYPE_VARCHAR);
        $this->addField("logo_id", Model::TYPE_INT);
        $this->addField("company_type", Model::TYPE_INT);
        $this->addField("companyname", Model::TYPE_VARCHAR);
        $this->addField("address", Model::TYPE_VARCHAR);
        $this->addField("hpenglish", Model::TYPE_VARCHAR);
        $this->addField("hphungarian", Model::TYPE_VARCHAR);
        $this->addField("contactperson1", Model::TYPE_VARCHAR);
        $this->addField("contactperson2", Model::TYPE_VARCHAR);
        $this->addField("jobtitle1", Model::TYPE_VARCHAR);
        $this->addField("jobtitle2", Model::TYPE_VARCHAR);
        $this->addField("email1", Model::TYPE_VARCHAR);
        $this->addField("email2", Model::TYPE_VARCHAR);
        $this->addField("phone1", Model::TYPE_VARCHAR);
        $this->addField("phone2", Model::TYPE_VARCHAR);
        $this->addField("fax1", Model::TYPE_VARCHAR);
        $this->addField("fax2", Model::TYPE_VARCHAR);
        $this->addField("activity", Model::TYPE_TEXT);
        $this->addField("photolink", Model::TYPE_VARCHAR);
        $this->addField("rotator_id", Model::TYPE_INT);
        $this->addField("gigabannerlink", Model::TYPE_VARCHAR);
        $this->addField("smallbannerexportlink", Model::TYPE_VARCHAR);
        $this->addField("smallbannerimportlink", Model::TYPE_VARCHAR);
        $this->addField("export", Model::TYPE_INT);
        $this->addField("import", Model::TYPE_INT);
        $this->addfield("active", Model::TYPE_INT, Model::MOD_ACCEPT);
        $this->addField("update_request", model::TYPE_TEXT, Model::MOD_UPDATE);
        $this->addField("spn_export_rotator", Model::TYPE_DATE);
        $this->addField("spn_import_rotator", Model::TYPE_DATE);
        $this->addField("spn_bl_rotator", Model::TYPE_DATE);
        $this->addField("spn_service_rotator", Model::TYPE_DATE);
        $this->addField("spn_ha_rotator", Model::TYPE_DATE);
        $this->addField("spn_service_selection", Model::TYPE_DATE);     // kivalasztott service kategoria szponzoralasa
        $this->addField("spn_service_minibanner", Model::TYPE_DATE);
        $this->addField("spn_service_logo", Model::TYPE_DATE);
        $this->addField("spn_ha_countrysearch", Model::TYPE_DATE);
        $this->addField("spn_bl_smallbanner", Model::TYPE_DATE);
        $this->addfield("productname", Model::TYPE_VARCHAR);    // csak kulfoldi magyar cegek eseten
        $this->addField("hs4", Model::TYPE_VARCHAR);    // csak kulfoldi magyar cegek eseten
        $this->addField("city", Model::TYPE_VARCHAR);
        $this->addField("street", Model::TYPE_VARCHAR);
        $this->addField("sttype", Model::TYPE_VARCHAR);
        $this->addField("houseno", Model::TYPE_VARCHAR);
        $this->addField("zipcode", Model::TYPE_VARCHAR);
        $this->addField("taxno", Model::TYPE_VARCHAR);
    }

    public function delete($id) {
        $query = "SELECT company_type FROM companies WHERE company_id=$id";
        $res = Database::executeQuery($query);
        $type = Database::getSimpleField($res);
        if ($type == "1") {
            $query = "DELETE FROM company_hs4_conn WHERE company_id=$id";
        } else if ($type == "2") {
            $query = "DELETE FROM recommended_services WHERE company_id=$id";
        } else if ($type == "3" || $type == "4") {
            $query =  "DELETE FROM businessleads WHERE company_id=$id";
        }
        Database::executeQuery($query);
        $query = "DELETE FROM companies WHERE company_id=$id";
        Database::executeQuery($query);

        $content = array();
        $stat = $this->getHeadStatistics();
        $content[] = "<?php";
        $content[] = 'global $stat;';
        $content[] = '$stat[\'companycount\'] = '.$stat['companycount'] . ";";
        $content[] = '$stat[\'categorycount\'] = '.$stat['categorycount'] . ";";
        $f = fopen("site/resources/stat.php", "w");
        for ($i = 0; $i < count($content); $i++) {
            fputs($f, $content[$i] . "\r\n");
        }
        fclose($f);
    }

    public function getSince($time, $condition = "") {
        $query = "SELECT * FROM companies ".
                 "LEFT OUTER JOIN company_hs4_conn ON company_hs4_conn.company_id=companies.company_id ".
                 "WHERE created_at >= '$time'"
        ;
        if ($condition != "") { $query .= " AND $condition"; }
        $query .= " LIMIT 0, 10";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        return $data;
    }

    public function validate($params) {
        $success = true;
        $fieldMsg = array();
        $today = getdate();
        $allowLessMonth = ($today['year'] == 2012 && $today['mon'] <= 9);
        foreach ($params as $key=>$value) {
            if ($key == "banner_ad") {
                if ($params['banner_id'] == "") {
                    $success = false;
                    $fieldMsg["banner_id"] = array(
                        code => 3,
                        msg => "You have to upload a valid banner to enable sponsoration!",
                    );
                }
            } else if ($key == "photo") {
                if ($params['photo_id'] == "") {
                    $success = false;
                    $fieldMsg["photo_id"] = array(
                        code => 3,
                        msg => "You have to upload a valid banner to enable sponsoration!",
                    );
                }
            } else if ($key == "logo_ad") {
                if ($params['logo_id'] == "") {
                    $success = false;
                    $fieldMsg["logo_id"] = array(
                        code => 3,
                        msg => "You have to upload a valid banner to enable sponsoration!",
                    );
                }
            } else if ($key == "logo_minibanner") {
                if ($params['minibanner_id'] == "") {
                    $success = false;
                    $fieldMsg["minibanner_id"] = array(
                        code => 3,
                        msg => "You have to upload a valid banner to enable sponsoration!",
                    );
                }
            } else if ($key == "topic_sponsor") {
                if ($params['topicsponsor_id'] == "") {
                    $success = false;
                    $fieldMsg["topicsponsor_id"] = array(
                        code => 3,
                        msg => "You have to upload a valid banner to enable sponsoration!",
                    );
                }
            } else if ($key == "logo") {
                if ($params['logo_id'] == "") {
                    $success = false;
                    $fieldMsg["logo_id"] = array(
                        code => 3,
                        msg => "You have to upload a valid banner to enable sponsoration!",
                    );
                }
            } else if ($key == "sponsor_export") {
                for ($i = 0; $i < count($params[$key]); $i++) {
                    if ($params[$key][$i] == "yes") {
                        if (!$allowLessMonth && $params['hs4sponsor_export_month'][$i] <= 1) {
                            $success = false;
                            $fieldMsg["hs4sponsor_export_month[$i]"] = array(
                                code => 1,
                                msg => "Discount time is over, you have to set 2 or more months!",
                            );
                        }
                    }
                }
            } else if ($key == "sponsor_import") {
                for ($i = 0; $i < count($params[$key]); $i++) {
                    if ($params[$key][$i] == "yes") {
                        if (!$allowLessMonth && $params['hs4sponsor_import_month'][$i] <= 2) {
                            $success = false;
                            $fieldMsg["hs4sponsor_import_month[$i]"] = array(
                                code => 2,
                                msg => "Discount time is over, you have to set 3 or more months!",
                            );
                        }
                    }
                }
            }
        }
        if ($success) {
            return validationSuccess();
        } else {
            return validationFailed($fieldMsg);
        }
    }

    public function sponsorationPayed($invoiceData) {
        $sponsors = array();
        $blSponsors = array();
        $hssponsors = array();

        for ($i = 0; $i < count($invoiceData); $i++) {
            if ($invoiceData[$i]['payment_subject'] == "export_rotator") {
                $sponsors['spn_export_rotator'] = $invoiceData[$i]['pay_deadline'];
                $sponsors['payed_export_rotator'] = 1;
            } elseif ($invoiceData[$i]['payment_subject'] == "import_rotator") {
                $sponsors['spn_import_rotator'] = $invoiceData[$i]['pay_deadline'];
                $sponsors['payed_import_rotator'] = 1;
            } elseif ($invoiceData[$i]['payment_subject'] == "businesslead_rotator") {
                $sponsors['spn_bl_rotator'] = $invoiceData[$i]['pay_deadline'];
                $sponsors['payed_bl_rotator'] = 1;
            } elseif ($invoiceData[$i]['payment_subject'] == "service_rotator") {
                $sponsors['spn_service_rotator'] = $invoiceData[$i]['pay_deadline'];
                $sponsors['payed_service_rotator'] = 1;
            } elseif ($invoiceData[$i]['payment_subject'] == "ha_rotator") {
                $sponsors['spn_ha_rotator'] = $invoiceData[$i]['pay_deadline'];
                $sponsors['payed_ha_rotator'] = 1;
            } else if ($invoiceData[$i]['payment_subject'] == "service_select") {
                $sponsors['spn_service_selection'] = $invoiceData[$i]['pay_deadline'];
                $sponsors['payed_service_selection'] = 1;
            } else if ($invoiceData[$i]['payment_subject'] == "service_logo") {
                $sponsors['spn_service_logo'] = $invoiceData[$i]['pay_deadline'];
                $sponsors['payed_service_logo'] = 1;
            } else if ($invoiceData[$i]['payment_subject'] == "product_photo") {
                $blSponsors[] = array(
                    'spn_product_photo' =>  $invoiceData[$i]['pay_deadline'],
                    'id' => $invoiceData[$i]['object_id'],
                );
            } else if ($invoiceData[$i]['payment_subject'] == "ha_countrysearch") {
                $sponsors['spn_ha_countrysearch'] = $invoiceData[$i]['pay_deadline'];
                $sponsors['payed_ha_countrysearch'] = 1;
            } else if ($invoiceData[$i]['payment_subject'] == "export_hs4") {
                $hssponsors[] = array(
                    hs4_id => $invoiceData[$i]['hs4_id'],
                    date => $invoiceData[$i]['pay_deadline'],
                );
            }
        }
        if (count($sponsors) > 0) {
            $query = QueryHelper::update("companies", $sponsors, "company_id=" . $invoiceData[0]['company_id']);
            Database::executeQuery($query);
        }
        if (count($blSponsors) > 0) {
            for ($i = 0; $i < count($blSponsors); $i++) {
                $query = "UPDATE businessleads SET spn_product_photo='" . $blSponsors[$i]['spn_product_photo'] . "', payed_product_photo=1 WHERE businesslead_id=" . $blSponsors[$i]['id'];
                Database::executeQuery($query);
            }
        }
        if (count($hssponsors) > 0) {
            for ($i = 0; $i < count($hssponsors); $i++) {
                $query = "UPDATE company_hs4_conn SET sponsor_end='" . $hssponsors[$i]['date'] . "' WHERE company_id=" . $invoiceData[0]['company_id'] . " AND hs4_id='" . $hssponsors[$i]['hs4_id'] . "'";
                Database::executeQuery($query);
            }
        }
    }

    public function getcompanyName($companyId) {
        $query = "SELECT companyname FROM companies WHERE company_id=" . $companyId;
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        return $data[0]['companyname'];
    }

    public function getRotatorCompaniesFor($type) {
        $sponsorField = "";
        if ($type == "export") {
            $sponsorField = "spn_export_rotator";
            $payedField = "payed_export_rotator";
        } else if ($type == "import") {
            $sponsorField = "spn_import_rotator";
            $payedField = "payed_import_rotator";
        } else if ($type == "businesslead") {
            $sponsorField = "spn_bl_rotator";
            $payedField = "payed_bl_rotator";
        } else if ($type == "service") {
            $sponsorField = "spn_service_rotator";
            $payedField = "payed_service_rotator";
        } else if ($type == "ha") {
            $sponsorField = "spn_ha_rotator";
            $payedField = "payed_ha_rotator";
        }
        $query = "SELECT company_id, hphungarian FROM companies WHERE $sponsorField > now() AND $payedField=1 AND banner_sponsor_allowed=1";
        $res = Database::executeQuery($query);
        $data = Database::getDatafromResource($res);
        return $data;
    }

    public function getCompanyByName($name) {
        $query = "SELECT * FROM companies WHERE companyname LIKE '$name%'";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        if ($data[0]['company_id'] == "") { return array(); }
        $query =  "SELECT * FROM company_hs4_conn WHERE company_id=" . $data[0]['company_id'];
        $res = Database::executeQuery($query);
        $data[0]['hsdata'] = Database::getDataFromResource($res);
        return $data[0];
    }

    public function getImage($type, $companyId) {
        $query = "";
        if ($type == "rotator") {
            $query = "SELECT rotator_id AS id FROM companies WHERE company_id=$companyId";
        } else if ($type == "banner") {
            $query = "SELECT banner_id AS id FROM companies WHERE company_id=$companyId";
        } else if ($type == "logo") {
            $query = "SELECT logo_id AS id FROM companies WHERE company_id=$companyId";
        }
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        return $data[0]['id'];
    }

    public function getNoHomepageCompaniesCount() {
        $query = "SELECT count(*) AS c FROM companies WHERE hphungarian IS NULL OR hpenglish IS NULL";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        return $data[0]['c'];
    }

    public function getNoHomepageCompanies() {
        $query = "SELECT company_id, companyname, email1 FROM companies WHERE hphungarian IS NULL OR hpenglish IS NULL";
        $res = Database::executeQuery($query);
        return Database::getDataFromResource($res);
    }

    public function getregisteredCompanies() {
        $query = "SELECT company_id, companyname, email1 FROM companies";
        $res = Database::executeQuery($query);
        return Database::getDataFromResource($res);
    }

    public function getCompaniesByHS2($hs2) {
    }

    public function getExporterImporterHungarianCompanies() {
        $query = "SELECT * FROM mv_group_5";
        $res = Database::executeQuery($query);
        return Database::getDataFromResource($res);
    }

    public function getHungarianCompaniesAbroad($country) {
        $query = "";
        if ($country == "") {
            $query = "SELECT company_id, companyname, email1 FROM companies WHERE is_habroad=1";
        } else {
            $query = "SELECT company_id, companyname, email1 FROM companies WHERE is_habroad=1 AND country='$country'";
        }
        $res = Database::executeQuery($query);
        return Database::getDataFromResource($res);
    }

    public function getCountries() {
        $query = "SELECT country FROM companies WHERE country <> 'Hungary' AND country IS NOT NULL";
        $res = Database::executeQuery($query);
        return Database::getDataFromResource($res);
    }

    public function getbusinessleaders() {
        $query = "SELECT company_id, companyname, email1 FROM companies WHERE company_id IN (SELECT company_id FROM businessleads)";
        $res = Database::executeQuery($query);
        return Database::getDataFromResource($res);
    }

    public function getItemByCompanyName($companyName) {
        $query = "SELECT * FROM companies WHERE companyname=$companyName";
        $res = Database::executeQuery($query);
        return Database::getDataFromResource($res);
    }

    public function renderAlias($record) {
        $skipFields = array(
            "logo",
        );
        foreach ($record as $key=>$value) {
            if ($key == "logo") { continue; }
            $result[$key] = $value;
        }
//        $result = $record;
        return $result;
    }

    public function removeHS4($company) {
        $query = "DELETE FROM company_hs4_conn WHERE company_id=$company";
        Database::executeQuery($query);
    }

    public function getHAbroadByCountry($countryName) {
        $query = "SELECT * FROM companies WHERE country='$countryName' ORDER BY spn_ha_countrysearch DESC, companyname";
        $res = Database::executeQuery($query);
        return Database::getDataFromResource($res);
    }

    public function getHAbroadCountByCountry($countryName) {
        $query = "SELECT count(*) FROM companies WHERE country='$countryName'";
        $res = Database::executeQuery($query);
        $data = Database::getsimpleField($res);
        return $data;
    }

    public function setHS4($company, $code, $impexp, $freetype) {
        $query = "";
        $query = "INSERT INTO company_hs4_conn (company_id, hs4_id, imp_exp, freetype_text) VALUES ($company, $code, $impexp, '$freetype')";
        Database::executeQuery($query);
    }

    public function setHAbroad($companyId) {
        $query = "UPDATE companies SET is_habroad=1 WHERE company_id=$companyId";
        Database::executeQuery($query);
    }

    public function changeHS4($companyId, $hs4Data) {
        $query = "UPDATE companies SET hs_update_request='" . json_encode($hs4Data) . "' WHERE company_id=$companyId";
        Database::executeQuery($query);
    }

    public function addRecommendedService($companyId, $serviceType, $serviceDescription) {
        $query = "INSERT INTO recommended_services (company_id, service_Type, service_description) VALUES ($companyId, $serviceType, '$serviceDescription') ";
        Database::executeQuery($query);
    }

    public function updateRecommendedService($companyId, $serviceType, $serviceDescription) {
        $result = "";
        $serviceDescription = str_replace(array("\r", "\n"), array(" ", " "), $serviceDescription);
        $changes = json_encode(array(
            service_type => $serviceType,
            service_description => $serviceDescription,
        ));
        $query = "SELECT count(*) AS c FROM recommended_services WHERE company_id=$companyId";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        if ($data[0]['c'] == 0) {
            $query = "INSERT INTO recommended_services (company_id, `update`) VALUES ($companyId, '$changes')";
            Database::executeQuery($query);
            $result = Database::getInsertedId();
        } else {
            $query = "UPDATE recommended_services SET `update`='$changes' WHERE company_id=$companyId";
            Database::executeQuery($query);
            $query = "SELECT id FROM recommended_services WHERE company_id=$companyId";
            $res = Database::executeQuery($query);
            $result = Database::getSimpleField($res);
        }
        return $result;
    }

    public function getCountriesForCompanies() {
        $query = "SELECT DISTINCT country AS text FROM companies WHERE country <> 'Hungary' && country <> 'Magyarország' ORDER BY country";
        $res = Database::executeQuery($query);
        return Database::getDataFromResource($res);
    }

    public function getPaidCompanies($gratis = false) {
        if ($gratis) {
            $this->setProjection(array(
                "company_id",
                "companyname",
                "logo_id",
                "in_rotator",
            ));
            return $this->loadAll("true");
        } else {
            $query = "SELECT company_id, companyname, logo_id, in_rotator FROM companies ".
                     "JOIN payment ON payment.company_id=companies.company_id ".
                     "WHERE payment.pay_subject=1 AND now() BETWEEN paid_from and paid_to"
            ;
            $res = Database::executeQuery($query);
            $data = Database::getDataFromResource($res);
            return $data;
        }
    }

    public function getPaidCompaniesCount($gratis = false) {
        if ($gratis) {
            return $this->getCount("true");
        } else {
            $query = "SELECT count(*) AS c FROM (".
                         "SELECT * FROM companies ".
                         "JOIN payment ON payment.company_id=companies.company_id ".
                         "WHERE payment.pay_subject=1 AND now() BETWEEN paid_from and paid_to".
                     ")x";
            ;
            $res = Database::executeQuery($query);
            $data = Database::getDataFromResource($res);
            return $data[0]['c'];
        }
    }

    public function getHeadStatistics() {
        $stat = array();
        $query = "SELECT count(*) AS c FROM companies WHERE uploaded <> 1";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        $stat['companycount'] = $data[0]['c'];
        $query = "SELECT count(*) as c FROM hs_codes";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        $stat['categorycount'] = $data[0]['c'];
        return $stat;
    }

    public function savesponsoration($companyId, $sponsorData) {
        $values = array(
            sponsor_update_request => json_encode($sponsorData),
        );
        $query = QueryHelper::update("companies", $values, "company_id=$companyId");
        Database::executeQuery($query);
    }

    public function getClientData($id) {
        $query = "SELECT users.eszamla_username, users.email, users.eszamla_username, companyname, taxno, country, city, street, sttype, houseno FROM companies ".
                 "JOIN users ON users.company_profile_id=companies.company_id ".
                 "WHERE company_id=$id"
        ;
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        if ($data[0]['country'] == "") { $data[0]['country'] = "Magyarország"; }
        return $data[0];
    }

    public function getPendingSponsorationFor($companyId) {
        $query = "SELECT * FROM payment WHERE company_id=$companyId";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        $result = array();
        for ($i = 0; $i < count($data); $i++) {
            if (strstr($data[$i]['payment_subject'], "_rotator") !== false) {
                $query = "SELECT rotator_id FROM companies WHERE company_id=$companyId";
                $res = Database::executeQuery($query);
                $rotator_id = Database::getSimpleField($res);
                $result[] = array(
                    type => "rotator",
                    rotator_id => $rotator_id,
                );
            } 
        }
        return $result;
    }

    public function acceptSponsorChanges($companyId) {
        $query = QueryHelper::update("companies", array(
            banner_sponsor_allowed => 1,
        ), "company_id=$companyId");
        Database::executeQuery($query);
        return;

    }

    public function getPendingSponsors() {
        $query = "SELECT company_id, companyname FROM companies WHERE sponsor_update_request IS NOT NULL AND sponsor_update_request <> ''";
        $res = Database::executeQuery($query);
        return Database::getDataFromResource($res);
    }

    public function getPendingSponsorsCount() {
        $query = "SELECT count(*) AS c FROM companies WHERE sponsor_update_request <> '' AND sponsor_update_request IS NOT NULL";
        $res = Database::executeQuery($query);
        return Database::getSimpleField($res);
    }

    public function getSponsorData($companyId) {
        $sponsorFields = array(
            "spn_export_rotator",
            "spn_import_rotator",
            "spn_bl_rotator",
            "spn_service_rotator",
            "spn_ha_rotator",
            "spn_service_selection",
            "spn_service_minibanner",
            "spn_service_logo",
            "spn_ha_countrysearch",
            "spn_bl_smallbanner",
            "spn_bl_logo",
            "payed_export_rotator",
            "payed_import_rotator",
            "payed_bl_rotator",
            "payed_service_rotator",
            "payed_ha_rotator",
            "payed_service_selection",
            "payed_service_minibanner",
            "payed_service_logo",
            "payed_ha_countrysearch",
            "payed_bl_smallbanner",
            "payed_bl_logo",
        );
        $query = "SELECT " . implode(",", $sponsorFields) . " FROM companies WHERE company_id=$companyId";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        return $data[0];
    }

    public function getRecommendedService($companyId, $previewMode = false) {
        if ($previewMode) {
            $query = "SELECT `update` FROM recommended_services WHERE company_id=$companyId";
            $res = Database::executeQuery($query);
            $data = Database::getDataFromResource($res);
            if ($data[0]['update'] != "") {
                return json_decode($data[0]['update'], true);
            }
        }
        $query = "SELECT service_type, service_description FROM recommended_services WHERE company_id=$companyId";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        return $data[0];
    }

    public function getBusinessLead($companyId, $previewMode = false) {
        $query = "SELECT * FROM businessleads WHERE company_id=$companyId";
        if (!$previewMode) {
            $query .= " AND accepted=1";
        }
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        return $data[0];
    }

    public function getHS4($company) {
        $subst = array(
            "u00e1" => "á",
            "u00ed" => "í",
            "u0171" => "ű",
            "u0151" => "ő",
            "u00fc" => "ü",
            "u00f6" => "ö",
            "u00fa" => "ú",
            "u00f3" => "ó",
            "u00e9" => "é",
        );
        $query = "SELECT hs_update_request, sponsor_end, hs4_id FROM companies JOIN company_hs4_conn ON company_hs4_conn.company_id=companies.company_id WHERE companies.company_id=$company";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        $spDates = array();
        for ($i = 0; $i < count($data); $i++) {
            $spDates[$data[$i]['hs4_id']] = $data[$i]['sponsor_end'];
        }
        $query = "SELECT sponsor_update_request FROM companies WHERE company_id=$company";
        $res = Database::executeQuery($query);
        $spData = json_decode(Database::getsimpleField($res), true);
        $spHash = array();
        for ($i = 0; $i < count($spData['export_hs4']); $i++) {
            $spHash[$spData['export_hs4'][$i]['hs4code']] = $spData['export_hs4'][$i]['date'];
        }
        for ($i = 0; $i < count($spData['import_hs4']); $i++) {
            $spHash[$spData['import_hs4'][$i]['hs4code']] = $spData['import_hs4'][$i]['date'];
        }
        if ($data[0]['hs_update_request'] != "") {
            $result = json_decode(str_replace(array_keys($subst), array_values($subst), $data[0]['hs_update_request']), true);
            for ($i = 0; $i < count($result); $i++) {
                if (!dateIsPast($result[$i]['sponsor_end']) || !dateIsPast($spDates[$result[$i]['hs4_id']])) {
                    $result[$i]['sponsor_status'] = "payed";
                }
            }
            return $result;
        }
        $query = "SELECT * FROM company_hs4_conn"
//                ." JOIN hs_codes ON hscode=company_hs4_conn.hs4_id" // TODO: ez a join miert kell?
                ." WHERE company_id=$company";
        $res = Database::executeQuery($query);
        $result = Database::getDataFromResource($res);
        for ($i = 0; $i < count($result); $i++) {
            if (!dateisPast($result[$i]['sponsor_end'])) {
                $result[$i]['sponsor_status'] = "payed";
            } else if ($spHash[$result[$i]['hs4_id']]) {
                $result[$i]['sponsor_status'] = "waiting";
            }
        }
        return $result;
    }

    public function getNoUserCompanies() {
        $query = "SELECT company_id, companyname, address, email1, contactperson1, hpenglish, hphungarian FROM companies LEFT OUTER JOIN users ON users.company_profile_id=companies.company_id WHERE users.username IS NULL ";
        $lmt = Database::calculateLimit($this->getPage(), $this->getRecordsPerPage());
        $limit = " LIMIT " . $lmt[0] . ", " . $lmt[1];
        $query .= $limit;
        $res = Database::executeQuery($query);
        return Database::getDataFromResource($res);
    }

    public function enableRotation($companyId) {
        $query = "UPDATE companies SET in_rotator=1 WHERE company_id=$companyId";
        Database::executeQuery($query);
    }
    
    public function disableRotation($companyId) {
        $query = "UPDATE companies SET in_rotator=0 WHERE company_id=$companyId";
        Database::executeQuery($query);
    }

    public function generateUsers() {
        $result = array();
        $query = "SELECT company_id, country, companyname, company_type, email1, contactperson1 FROM companies LEFT OUTER JOIN users ON users.company_profile_id=companies.company_id WHERE users.username IS NULL ";
        $res = Database::executeQuery($query);
        $data = Database::getDatafromResource($res);
        $today = date("Y-m-d");
        $ndx = 1;
        $subst = array(
            " " => "_",
            ". " => "",
            "." => "",
            "'" => "_",
            "-" => "_",
        );
        $emailContent = "<table>";
        for ($i = 0; $i < count($data); $i++) {
            $userdata = array();
            $name = explode(" ", $data[$i]['contactperson1']);
            if ($name[0] == "Dr.") {
                $userdata['title'] = $name[0];
                $userdata['firstname'] = $name[1];
                $userdata['lastname'] = $name[2];
            } else {
                $userdata['firstname'] = $name[0];
                $userdata['lastname']  = $name[1];
            }
            $userdata['email'] = $data[$i]['email1'];
            $emailparts = explode("@", $userdata['email']);
            $emailuser = $emailparts[0];
            $username = removeAccents($data[$i]['companyname']);
            if ($username == "") { $username = "impexuser"; }
            $tmp = explode(" ", $username);
            $username = $tmp[0];
            $username = str_replace(array_keys($subst), array_values($subst), strtolower($username)) . "_" . $i;
            $userdata['username'] = $username;
            $pw = substr(md5($userdata['username']), 0, 10);
            $emailContent .= "<tr><td>$username</td><td>$pw</td></tr>";
            $userdata['password'] = md5($pw);
            $userdata['registered'] = $today;
            $userdata['wantnewsletter'] = 1;
            if ($data[$i]['country'] != "Hungary" && $data[$i]['country'] != "") {
                $userdata['registertype'] = 5;
            } else if ($data[$i]['company_type'] == "0") {
                $userdata['registertype'] = "1";
            } else {
                $userdata['registertype'] = $data[$i]['company_type'];
            }
            $userdata['company_profile_id'] = $data[$i]['company_id'];
            $userdata['bulkcreated'] = 1;
            $userdata['decryptedpw'] = $pw;
            $query = QueryHelper::insertInto("users", $userdata);
            Database::executeQuery($query);
            $ndx++;
            $result[] = array(
                companyname => $data[$i]['companyname'],
                email => $userdata['email'],
                firstname => $userdata['firstname'],
                lastname => $userdata['lastname'],
                username => $userdata['username'],
                password => $pw,
                title => $userdata['title'],
            );
        }
        $emailContent .= "</table>";
        $admins = getConfigParameter("site", "administrator");
        $adminservice = getConfigParameter("site", "adminService");
        $f = fopen("site/resources/upload/attachment.html", "w");
        fputs($f, "<html><body>$emailContent</body></html>");
        fclose($f);
        for ($i = 0; $i < count($admins); $i++) {
            $emailContent2 = "Dear " . $admins[$i]['name'] . ",<br> you can find the bulk generated user list with passwords in the attached HTML file.<br>";
            $emailContent2 .= $adminservice['emailSign'];
            sendEmailWithContent($admins[$i]['email'], "Hungarian Exporters admin service - Bulk user creation", $emailContent2, array(
                "site/resources/upload/attachment.html",
            ));
        }
        unlink("site/resources/upload/attachment.html");
        return $result;
    }

    public function getNoUsercompaniesCount() {
        $query = "SELECT count(*) AS c FROM (SELECT company_id, companyname, address, email1, contactperson1, hpenglish, hphungarian FROM companies LEFT OUTER JOIN users ON users.company_profile_id=companies.company_id WHERE users.username IS NULL)x";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        return $data[0]['c'];
    }

    public function getUploadedCompanies() {
        $query = "SELECT company_id, companyname, address, email1, contactperson1, hpenglish, hphungarian FROM companies WHERE uploaded=1 ";
        $lmt = Database::calculateLimit($this->getPage(), $this->getRecordsPerPage());
        $limit = " LIMIT " . $lmt[0] . ", " . $lmt[1];
        $query .= $limit;
        $res = Database::executeQuery($query);
        return Database::getDatafromresource($res);
    }

    public function importUploadedCompanies() {
        $query = "UPDATE companies SET uploaded=0 WHERE uploaded=1"; Database::executeQuery($query);
        $query = "UPDATE company_hs4_conn SET uploaded=0 WHERE uploaded=1"; Database::executeQuery($query);
        $query = "UPDATE businessleads SET uploaded=0,accepted=1,accepted_at=now() WHERE uploaded=1"; Database::executeQuery($query);
        $query = "UPDATE recommended_services SET uploaded=0,accepted=1,accepted_at=now() WHERE uploaded=1"; Database::executeQuery($query);
    }

    public function rollbackUploadedCompanies() {
        $query = "DELETE FROM companies WHERE uploaded=1"; Database::executeQuery($query);
        $query = "DELETE FROM company_hs4_conn WHERE uploaded=1"; Database::executeQuery($query);
        $query = "DELETE FROM recommended_services WHERE uploaded=1"; Database::executeQuery($query);
        $query = "DELETE FROM businessleads WHERE uploaded=1"; Database::executeQuery($query);
    }

    public function getUploadedCompaniesCount() {
        $query = "SELECT count(*) AS c FROM companies WHERE uploaded=1";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        return $data[0]['c'];
    }

    public function getPreviewData($id) {
        $subst = array(
            "u00e1" => "á",
            "u00ed" => "í",
            "u0171" => "ű",
            "u0151" => "ő",
            "u00fc" => "ü",
            "u00f6" => "ö",
            "u00fa" => "ú",
            "u00f3" => "ó",
            "u00e9" => "é",
        );
        $query = "SELECT update_request FROM companies WHERE company_id=$id";
        $res = Database::executeQuery($query);
        $rec = mysql_fetch_assoc($res);
        $data = json_decode(str_replace(array_keys($subst), array_values($subst), $rec[0]['update_request']), true);
        $rec = $this->applyTypes($data);
        return $this->renderAlias($rec);
    }

    public function getExportCompaniesCountByFreeText($freetext) {
        $query = "SELECT count(*) AS c FROM (".
                 "SELECT DISTINCT company_id FROM mv_company_hs ".
                 "WHERE imp_exp=2 AND description_en LIKE '%$freetext%' OR description_hu LIKE '%$freetext%' OR freetype_text LIKE '%$freetext%')x";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        return $data[0]['c'];
    }

    public function getCompaniesLike($likestr) {
        $query = "SELECT companyname, company_id FROM companies WHERE companyname LIKE '%$likestr%' ORDER BY companyname";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromresource($res);
        return $data;
    }

    public function getImportCompaniesLike($likestr) {
        $query = "SELECT DISTINCT companyname, companies.company_id FROM companies ".
                 "JOIN company_hs4_conn ON company_hs4_conn.company_id=companies.company_id ".
                 "WHERE imp_exp=1 AND companyname LIKE '%$likestr%' ".
                 "ORDER BY companyname"
        ;
        $res = Database::executeQuery($query);
        $data = Database::getDataFromresource($res);
        return $data;
    }

    public function getExportCompaniesLike($likestr) {
        $query = "SELECT DISTINCT companyname, companies.company_id FROM companies ".
                 "JOIN company_hs4_conn ON company_hs4_conn.company_id=companies.company_id ".
                 "WHERE imp_exp=2 AND companyname LIKE '%$likestr%' ".
                 "ORDER BY companyname"
        ;
        $res = Database::executeQuery($query);
        $data = Database::getDataFromresource($res);
        return $data;
    }

    public function getImportCompaniesCountByFreeText($freetext) {
        $query = "SELECT count(*) AS c FROM (".
                 "SELECT DISTINCT company_id FROM mv_company_hs ".
                 "WHERE imp_exp=1 AND (description_en LIKE '%$freetext%' OR description_hu LIKE '%$freetext%' OR freetype_text LIKE '%$freetext%'))x";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        return $data[0]['c'];
    }

    public function getImportCompaniesByFreeText($page, $freetext) {
        $query = "";
        $start = ($page-1) * 10;
        $query = "SELECT DISTINCT mv_company_hs.company_id FROM mv_company_hs ".
                 "JOIN companies ON companies.company_id=mv_company_hs.company_id ".
                 "WHERE imp_exp=1 AND (description_en LIKE '%$freetext%' OR description_hu LIKE '%$freetext%' OR freetype_text LIKE '%$freetext%') ORDER BY logo_id DESC"
        ;
        $res = Database::executeQuery($query);
        $comp_id = Database::getDataFromResource($res);
        $companyidlist = array();
        for ($i = 0; $i < count($comp_id); $i++) {
            $companyidlist[] = $comp_id[$i]['company_id'];
        }
        if (count($companyidlist) == 0) { return array(); }
        $query = "SELECT * FROM companies WHERE company_id IN (" . implode(",", $companyidlist) . ") ORDER BY logo_id DESC, companyname";
        $query .= " LIMIT $start, 10";
        $res = Database::executeQuery($query);
        $result = Database::getDataFromResource($res);
        $query = "SELECT DISTINCT company_id, hs4_id, freetype_text FROM company_hs4_conn WHERE imp_exp=1 AND company_id IN (" . implode(",", $companyidlist) . ")";
        $res = Database::executeQuery($query);
        $hsresult = Database::getDataFromResource($res);
        for ($i = 0; $i < count($hsresult); $i++) {
            for ($ii = 0; $ii < count($result); $ii++) {
                if ($result[$ii]['company_id'] == $hsresult[$i]['company_id']) {
                    if (!is_array($result[$ii]['hs4codes'])) {
                        $result[$ii]['hs4codes'] = array();
                    }
                    if ($hsresult[$i]['hs4_id'] != "0") {
                        $result[$ii]['hs4codes'][] = $hsresult[$i]['hs4_id'];
                    }
                    $result[$ii]['freetype_text'] = $hsresult[$i]['freetype_text'];
                }
            }
        }
        return $result;
    }

    public function getUpdateRequests() {
        $query = "SELECT company_id, companyname FROM companies WHERE company_type=1 AND (update_request is not null OR hs_update_request IS NOT NULL) AND (update_request <> '' OR hs_update_request <> '')";
        $res = Database::executeQuery($query);
        $result = Database::getDataFromResource($res);
        $data = array();
        for ($i = 0; $i < count($result); $i++) {
            $data[$i]['id'] = $result[$i]['company_id'];
            $data[$i]['cell'] = array(
                $result[$i]['companyname']
            );
        }
        return $data;
    }

    public function getHAUpdateRequests() {
        $query = "SELECT company_id, companyname FROM companies WHERE is_habroad=1 AND (update_request is not null OR hs_update_request IS NOT NULL) AND (update_request <> '' OR hs_update_request <> '')";
        $res = Database::executeQuery($query);
        $result = Database::getDataFromResource($res);
        $data = array();
        for ($i = 0; $i < count($result); $i++) {
            $data[$i]['id'] = $result[$i]['company_id'];
            $data[$i]['cell'] = array(
                $result[$i]['companyname']
            );
        }
        return $data;
    }

    public function getCompanyEmails($idList = array()) {
        $query = "";
        if (count($idList) == 0) {
            $query = "SELECT company_id, companyname, email1 FROM companies ";
            $lmt = Database::calculateLimit($this->getPage(), $this->getRecordsPerPage());
            $limit = " LIMIT " . $lmt[0] . ", " . $lmt[1];
            $query .= $limit;
        } else {
            $query = "SELECT email1 FROM companies WHERE company_id IN (" . implode(",", $idList) . ")";
        }
        $res = Database::executeQuery($query);
        $result = Database::getDataFromResource($res);
        return $result; 
    }

    public function getCompanyEmailCount() {
        $query = "SELECT count(*) AS c FROM (".
                    "SELECT company_id, companyname, email FROM companies ".
                    "JOIN users ON users.company_profile_id=companies.company_id ".
                 ")x"
        ;
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        return $data[0]['c'];
    }

    public function acceptChanges($company_id) {
        $data = $this->load($company_id, true);
        $updatestmt = QueryHelper::update("companies", $data, "company_id=$company_id");
        $query = "select hs_update_request from companies WHERE company_id=$company_id";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        if ($data[0]['hs_update_request'] != "") {
            $query = "SELECT hs4_id, imp_exp, sponsor_end FROM company_hs4_conn WHERE company_id=$company_id";
            $res = Database::executeQuery($query);
            $sponsorData = Database::getDataFromResource($res);
            $query = "DELETE FROM company_hs4_conn WHERE company_id=$company_id";
            Database::executeQuery($query);
            $subst = array(
                "u00e1" => "á",
                "u00ed" => "í",
                "u0171" => "ű",
                "u0151" => "ő",
                "u00fc" => "ü",
                "u00f6" => "ö",
                "u00fa" => "ú",
                "u00f3" => "ó",
                "u00e9" => "é",
            );
            $hs4data = json_decode(str_replace(array_keys($subst), array_values($subst), $data[0]['hs_update_request']), true);
            $sponsorHash = array();
            for ($i = 0; $i < count($sponsorData); $i++) {
                $sponsorHash[$sponsorData[$i]['hs4_id'] . "_" . $sponsorData[$i]['imp_exp']] = $sponsorData[$i]['sponsor_end'];
            }
            for ($i = 0; $i < count($hs4data); $i++) {
                if ($hs4data[$i]['hs4_id'] == "") { continue; }
                $hs4data[$i]['company_id'] = $company_id;
                $hs4data[$i]['sponsor_end'] = $sponsorHash[$hs4data[$i]['hs4_id'] . "_" . $hs4data[$i]['imp_exp']];
                $query = QueryHelper::insertInto("company_hs4_conn", $hs4data[$i]);
                Database::executeQuery($query);
            }
        }
        $changes = $this->getChanges($company_id);
        if (count($changes) > 0) {
            $updatestmt = QueryHelper::update("companies", $changes, "company_id=$company_id");
        }
        Database::executeQuery($updatestmt);
        $query = "UPDATE companies SET update_request=null, hs_update_request=null WHERE company_id=$company_id";
        Database::executeQuery($query);
    }

    public function getNotUpdatedCompanies() {
        $query = "SELECT companyname, company_id, email1 FROM companies JOIN users ON users.company_profile_id=companies.company_id WHERE users.profileupdated is null";
        $res = Database::executeQuery($query);
        return Database::getDataFromResource($res);
    }

    public function rejectChanges($company_id) {
        $query = "UPDATE companies SET update_request=null, hs_update_request=null WHERE company_id=$company_id";
        Database::executeQuery($query);
    }

    public function getChanges($company_id) {
        $result = array();
        $query = "SELECT * FROM companies WHERE company_id=$company_id";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromREsource($res);
        $subst = array(
            "u00e1" => "á",
            "u00ed" => "í",
            "u0171" => "ű",
            "u0151" => "ő",
            "u00fc" => "ü",
            "u00f6" => "ö",
            "u00fa" => "ú",
            "u00f3" => "ó",
            "u00e9" => "é",
        );
        $change = json_decode(str_replace(array_keys($subst), array_values($subst), $data[0]['update_request']), true);
        if ($change != "") {
            $fieldList = $this->getfieldNames();
            foreach ($change as $key=>$value) {
                if (!in_array($key, $fieldList)) { continue; }
                if ($value != $data[0][$key]) {
                    if ($value == "no" && $data[0][$key] == "0") { continue; }
                    $result[$key] = $value;
                }
            }
        }
        return $result;
    }

    public function getUpdateRequestCount() {
        $query = "SELECT count(*) AS c FROM companies WHERE update_request IS NOT NULL AND updatE_request <> ''";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromREsource($res);
        return $data[0]['c'];
    }

    public function getHAUpdateRequestCount() {
        $query = "SELECT count(*) AS c FROM companies WHERE is_habroad=1 AND update_request IS NOT NULL AND updatE_request <> ''";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromREsource($res);
        return $data[0]['c'];
    }

    public function getHACompaniesLike($likestr, $dependant = "") {
        $query = "";
        if ($dependant != "") {
            $query = "SELECT companyname, company_id FROM companies WHERE companyname LIKE '%$likestr%' AND is_habroad=1 AND country='$dependant'";
        } else {
            $query = "SELECT companyname, company_id FROM companies WHERE companyname LIKE '%$likestr%' AND is_habroad=1";
        }
        $query .= " ORDER BY companyname";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromresource($res);
        return $data;
    }

    public function save($id, $data) {
        if ($id) {
            $query = "UPDATE companies SET update_request='" . iconv("UTF-8", "UTF-8//IGNORE", json_encode($data)) . "' WHERE company_id=$id";
            Database::executeQuery($query);
        } else {
            parent::save($id, $data);
        }
    }

    public function updateMV() {
        $query = "TRUNCATE mv_company_hs";
        Database::executeQuery($query);
        $query = "insert into mv_company_hs (company_id,imp_exp,freetype_text,hscode,description_en,description_hu) ".
                 "select companies.company_id, company_hs4_conn.imp_exp, company_hs4_conn.freetype_text, hs_codes.hscode, hs_codes.description_en, hs_codes.description_hu from companies ".
                 "join company_hs4_conn on company_hs4_conn.company_id=companies.company_id ".
                 "join hs_codes on company_hs4_conn.hs4_id=hs_codes.hscode"
        ;
        Database::executeQuery($query);
    }

    public function setService($companyId, $serviceId, $description) {
        $query = "DELETE FROM company_service_conn WHERE company_id=$companyId";
        Database::executeQuery($query);
        $query = "INSERT INTO company_service_conn (company_id, service_id, description) VALUES ($companyId, $serviceId, '$description')";
        Database::executeQuery($query);
    }

    public function getStatistics() {
        $result = array();

        $query = "SELECT count(*) AS c FROM company_hs4_conn ".
                 "JOIN companies ON companies.company_id=company_hs4_conn.company_id ".
                 "WHERE companies.active=1 AND company_hs4_conn.imp_exp=1"
        ;
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        $result['export'] = $data[0]['c'];

        $query = "SELECT count(*) AS c FROM company_hs4_conn ".
                 "JOIN companies ON companies.company_id=company_hs4_conn.company_id ".
                 "WHERE companies.active=1 AND company_hs4_conn.imp_exp=2"
        ;
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        $result['import'] = $data[0]['c'];

        $query = "SELECT count(*) AS c FROM company_service_conn ".
                 "JOIN companies ON companies.company_id=company_service_conn.company_id ".
                 "WHERE companies.active=1"
        ;
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        $result['services'] = $data[0]['c'];

        $query = "SELECT count(*) AS c FROM businessleads ".
                 "JOIN companies ON companies.company_id=businessleads.company_id ".
                 "WHERE companies.active=1"
        ;
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        $result['businessleads'] = $data[0]['c'];

        $query = "SELECT count(*) AS c FROM companies WHERE is_habroad=1";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        $result['habroad'] = $data[0]['c'];

        return $result;
    }

    public function getService($companyId) {
        $query = "SELECT * FROM company_service_conn WHERE company_id=$companyId";
        $res = Database::executeQuery($query);
        $result = Database::getDataFromResource($res);
        return $result[0];
    }

    public function setBusinessLead($company_id, $productname, $description, $buysell, $hs4code, $quantity = 0, $unit = "", $corigin = "", $cdest = "") {
        $today = date("Y-m-d");
        if ($quantity == "") { $quantity = 0; }
        $query = "INSERT INTO businessleads (".
                 "company_id, productname, description, buysell, hs4, quantity, unit, country_origin, country_dest, postdate".
                 ") VALUES (".
                 "$company_id, '$productname', '$description', $buysell, '$hs4code', $quantity, '$unit', '$corigin', '$cdest', '$today'".
                 ")"
        ;
        Database::executeQuery($query);
    }

    public function getExportCompaniesByFreeText($page, $freetext) {
        $query = "";
        $start = ($page-1) * 10;
        $query = "SELECT DISTINCT mv_company_hs.company_id FROM mv_company_hs ".
                 "JOIN companies ON companies.company_id=mv_company_hs.company_id ".
                 "WHERE imp_exp=2 AND (description_en LIKE '%$freetext%' OR description_hu LIKE '%$freetext%' OR freetype_text LIKE '%$freetext%') ORDER BY logo_id DESC, companies.companyname"
        ;
        $query .= " LIMIT $start, 10";
        $res = Database::executeQuery($query);
        $comp_id = Database::getDataFromResource($res);
        $companyidlist = array();
        for ($i = 0; $i < count($comp_id); $i++) {
            $companyidlist[] = $comp_id[$i]['company_id'];
        }
        if (count($companyidlist) == 0) { return array(); }
        $query = "SELECT * FROM companies WHERE company_id IN (" . implode(",", $companyidlist) . ") ORDER BY logo_id DESC, companyname";
        $res = Database::executeQuery($query);
        $result = Database::getDataFromResource($res);
        $query = "SELECT DISTINCT company_id, hs4_id, freetype_text FROM company_hs4_conn WHERE imp_exp=2 AND company_id IN (" . implode(",", $companyidlist) . ")";
        $res = Database::executeQuery($query);
        $hsresult = Database::getDataFromResource($res);
        for ($i = 0; $i < count($hsresult); $i++) {
            for ($ii = 0; $ii < count($result); $ii++) {
                if ($result[$ii]['company_id'] == $hsresult[$i]['company_id']) {
                    if (!is_array($result[$ii]['hs4codes'])) {
                        $result[$ii]['hs4codes'] = array();
                    }
                    if ($hsresult[$i]['hs4_id'] != "0") {
                        $result[$ii]['hs4codes'][] = $hsresult[$i]['hs4_id'];
                    }
                    $result[$ii]['freetype_text'] = $hsresult[$i]['freetype_text'];
                }
            }
        }
        return $result;
    }

    public function addRotatorBanner($companyId, $fileId) {
        $query = QueryHelper::update("companies", array("rotator_id"=> $fileId), "company_id=$companyId");
        Database::executeQuery($query);
    }

    public function getExportCompanies($page, $hs2, $hs4 = "") {
        $query = "";
        $start = ($page-1) * 10;
        if ($hs2 == $hs4) {
            $query = "SELECT DISTINCT companies.* FROM companies ".
                     "JOIN company_hs4_conn ON companies.company_id=company_hs4_conn.company_id ".
                     "JOIN hs_codes ON hs_codes.hscode=company_hs4_conn.hs4_id ".
                     "WHERE company_hs4_conn.imp_exp=2 AND chapter='$hs2' ".
                     "ORDER BY sponsor_end DESC, companies.logo_id DESC, companyname"
            ;
        } else if ($hs4 != "") {
            $query = "SELECT DISTINCT companies.* FROM companies ".
                     "JOIN company_hs4_conn ON companies.company_id=company_hs4_conn.company_id ".
                     "WHERE company_hs4_conn.imp_exp=2 AND (company_hs4_conn.hs4_id='$hs4' OR company_hs4_conn.hs4_id='$hs4 S') ".
                     "ORDER BY sponsor_end DESC, companies.logo_id DESC, companyname"
            ;
        } else {
            $query = "SELECT DISTINCT companies.* FROM companies ".
                     "JOIN company_hs4_conn ON companies.company_id=company_hs4_conn.company_id ".
                     "WHERE company_hs4_conn.imp_exp=2 AND (company_hs4_conn.hs4_id LIKE '" . $hs2 . "__' OR company_hs4_conn.hs4_id LIKE '" . $hs2 . "_' OR company_hs4_conn.hs4_id='$hs2') ".
                     "ORDER BY sponsor_end DESC, companies.logo_id DESC, companyname"
            ;
        }
        $query .= " LIMIT $start, 10";
        $res = Database::executeQuery($query);
        $result = Database::getDataFromResource($res);
        if (count($result) == 0) {
            $query = "SELECT DISTINCT companies.* FROM companies ".
                     "JOIN company_hs4_conn ON companies.company_id=company_hs4_conn.company_id ".
                     "WHERE company_hs4_conn.imp_exp=2 AND company_hs4_conn.hs4_id='$hs2' ".
                     "ORDER BY sponsor_end DESC, companies.logo_id DESC, companyname"
            ;
            $query .= " LIMIT $start, 10";
            $res = Database::executeQuery($query);
            $result = Database::getDataFromResource($res);
        }
        $idlist = array();
        for ($i = 0; $i < count($result); $i++) {
            $idlist[] = $result[$i]['company_id'];
        }
        if (count($idlist) == 0) { return array(); }
        $query = "SELECT DISTINCT company_id, hs4_id, freetype_text, sponsor_end FROM company_hs4_conn WHERE imp_exp=2 AND company_id IN (" . implode(",", $idlist) . ")";
        $res = Database::executeQuery($query);
        $hsresult = Database::getDataFromResource($res);
        $sponsorCounter = 0;
        for ($i = 0; $i < count($hsresult); $i++) {
            for ($ii = 0; $ii < count($result); $ii++) {
                if ($result[$ii]['company_id'] == $hsresult[$i]['company_id']) {
                    if (!is_array($result[$ii]['hs4codes'])) {
                        $result[$ii]['hs4codes'] = array();
                    }
                    if ($hsresult[$i]['hs4_id'] != "0") {
                        $result[$ii]['hs4codes'][] = $hsresult[$i]['hs4_id'];
                    }
                    if (!dateIsPast($hsresult[$i]['sponsor_end']) && $hsresult[$i]['hs4_id'] == $hs4 && $sponsorCounter < 5) {
                        $result[$ii]['hssponsored'] = true;
                        $sponsorCounter++;
                    }
                    $result[$ii]['freetype_text'] = $hsresult[$i]['freetype_text'];
                }
            }
        }
        return $result;
    }

    public function getImportCompanyProducts($companyId) {
        $query = "SELECT * FROM company_hs4_conn WHERE company_id=$companyId AND imp_exp=1";
        $res = Database::executeQuery($query);
        $hsresult = Database::getDataFromREsource($res);
        $result = array();
        for ($i = 0; $i < count($hsresult); $i++) {
            if (!is_array($result[$ii]['hs4codes'])) {
                $result['hs4codes'] = array();
            }
            if ($hsresult[$i]['hs4_id'] != "0") {
                $result['hs4codes'][] = $hsresult[$i]['hs4_id'];
            }
            $result['freetype_text'] = $hsresult[$i]['freetype_text'];
        }
        return $result;
    }

    public function getExportCompanyProducts($companyId) {
        $query = "SELECT * FROM company_hs4_conn WHERE company_id=$companyId AND imp_exp=2";
        $res = Database::executeQuery($query);
        $hsresult = Database::getDataFromREsource($res);
        $result = array();
        for ($i = 0; $i < count($hsresult); $i++) {
            if (!is_array($result['hs4codes'])) {
                $result['hs4codes'] = array();
            }
            if ($hsresult[$i]['hs4_id'] != "0") {
                $result['hs4codes'][] = $hsresult[$i]['hs4_id'];
            }
            $result['freetype_text'] = $hsresult[$i]['freetype_text'];
        }
        return $result;
    }

    public function getImportCompanies($page, $hs2, $hs4 = "") {
        $query = "";
        $start = ($page-1) * 10;
        if ($hs2 == $hs4) {
            $query = "SELECT DISTINCT companies.* FROM companies ".
                     "JOIN company_hs4_conn ON companies.company_id=company_hs4_conn.company_id ".
                     "JOIN hs_codes ON hs_codes.hscode=company_hs4_conn.hs4_id ".
                     "WHERE company_hs4_conn.imp_exp=1 AND chapter='$hs2' ".
                     "ORDER BY sponsor_end DESC, companies.logo_id DESC, companyname"
            ;
        } else if ($hs4 != "") {
            $query = "SELECT DISTINCT companies.* FROM companies ".
                     "JOIN company_hs4_conn ON companies.company_id=company_hs4_conn.company_id ".
                     "WHERE company_hs4_conn.imp_exp=1 AND (company_hs4_conn.hs4_id='$hs4' OR company_hs4_conn.hs4_id='$hs4 S') ".
                     "ORDER BY sponsor_end DESC, companies.logo_id DESC, companyname"
            ;
        } else {
            $query = "SELECT DISTINCT companies.* FROM companies ".
                     "JOIN company_hs4_conn ON companies.company_id=company_hs4_conn.company_id ".
                     "WHERE company_hs4_conn.imp_exp=1 AND (company_hs4_conn.hs4_id LIKE '" . $hs2 . "__' OR company_hs4_conn.hs4_id LIKE '" . $hs2 . "_' OR company_hs4_conn.hs4_id='$hs2') ".
                     "ORDER BY sponsor_end DESC, companies.logo_id DESC, companyname"
            ;
        }
        $query .= " LIMIT $start, 10";
        $res = Database::executeQuery($query);
        $result = Database::getDataFromResource($res);
        if (count($result) == 0) {
            $query = "SELECT DISTINCT companies.* FROM companies ".
                     "JOIN company_hs4_conn ON companies.company_id=company_hs4_conn.company_id ".
                     "WHERE company_hs4_conn.imp_exp=1 AND company_hs4_conn.hs4_id='$hs2'" .
                     "ORDER BY sponsor_end DESC, companies.logo_id DESC, companyname"
            ;
            $query .= " LIMIT $start, 10";
            $res = Database::executeQuery($query);
            $result = Database::getDataFromResource($res);
        }
        $idlist = array();
        for ($i = 0; $i < count($result); $i++) {
            $idlist[] = $result[$i]['company_id'];
        }
        if (count($idlist) == 0) { return array(); }
        $query = "SELECT DISTINCT sponsor_end, company_id, hs4_id, freetype_text FROM company_hs4_conn WHERE imp_exp=1 AND company_id IN (" . implode(",", $idlist) . ")";
        $res = Database::executeQuery($query);
        $hsresult = Database::getDataFromResource($res);

        for ($i = 0; $i < count($hsresult); $i++) {
            for ($ii = 0; $ii < count($result); $ii++) {
                if ($result[$ii]['company_id'] == $hsresult[$i]['company_id']) {
                    if (!is_array($result[$ii]['hs4codes'])) {
                        $result[$ii]['hs4codes'] = array();
                    }
                    if ($hsresult[$i]['hs4_id'] != "0") {
                        $result[$ii]['hs4codes'][] = $hsresult[$i]['hs4_id'];
                    }
                    if (!dateIsPast($hsresult[$i]['sponsor_end'])) {
                        $result[$ii]['hssponsored'] = true;
                    }
                    $result[$ii]['freetype_text'] = $hsresult[$i]['freetype_text'];
                }
            }
        }
        return $result;
    }

    public function getServiceCount($selectedService) {
        $query = "SELECT count(*) AS c FROM ( ".
                 "SELECT DISTINCT company_id FROM company_service_conn WHERE service_id=$selectedService".
                 ")x";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        return intval($data[0]['c'], 10);
    }

    public function getServiceCompanies($page, $service) {
        $query = "SELECT DISTINCT company_id FROM company_service_conn WHERE service_id=$service";
        $query .= " LIMIT $page, 10";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        if (count($data) == 0) { return array(); }
        $idlist = array();
        for ($i = 0; $i < count($data); $i++) {
            $idlist[] = $data[$i]['company_id'];
        }
        $query = "SELECT * FROM companies WHERE company_id IN (" . implode(",", $idlist) . ")";
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        return $data;
    }

    public function getExportCompaniesCount($hs2, $hs4 = "") {
        $query = "";
        if ($hs2 == $hs4) {
            $query = "SELECT count(*) AS c FROM ( ".
                     "SELECT DISTINCT companies.company_id FROM companies ".
                     "JOIN company_hs4_conn ON companies.company_id=company_hs4_conn.company_id ".
                     "JOIN hs_codes ON hs_codes.hscode=company_hs4_conn.hs4_id ".
                     "WHERE company_hs4_conn.imp_exp=2 AND chapter='$hs2')x "
            ;
        } else if ($hs4 != "") {
            $query = "SELECT count(*) AS c FROM ( ".
                     "SELECT DISTINCT companies.company_id FROM companies ".
                     "JOIN company_hs4_conn ON companies.company_id=company_hs4_conn.company_id ".
                     "WHERE company_hs4_conn.imp_exp=2 AND (company_hs4_conn.hs4_id='$hs4' OR company_hs4_conn.hs4_id='$hs4 S'))x "
            ;
        } else {
            $query = "SELECT count(*) AS c FROM ( ".
                     "SELECT DISTINCT companies.company_id FROM companies ".
                     "JOIN company_hs4_conn ON companies.company_id=company_hs4_conn.company_id ".
                     "WHERE company_hs4_conn.imp_exp=2 AND company_hs4_conn.hs4_id LIKE '" . $hs2 . "__' OR company_hs4_conn.hs4_id LIKE '" . $hs2 . "_' OR company_hs4_conn.hs4_id='$hs2')x"
            ;
        }
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        if ($data[0]['c'] == "0") {
            $query = "SELECT count(*) AS c FROM (".
                     "SELECT DISTINCT companies.company_id FROM companies ".
                     "JOIN company_hs4_conn ON companies.company_id=company_hs4_conn.company_id ".
                     "WHERE company_hs4_conn.imp_exp=2 AND company_hs4_conn.hs4_id='$hs2')x"
            ;
            $res = Database::executeQuery($query);
            $data = Database::getDataFromResource($res);
        }
        return intval($data[0]['c'], 10);
    }

    public function getImportCompaniesCount($hs2, $hs4 = "") {
        $query = "";
        if ($hs2 == $hs4) {
            $query = "SELECT count(*) AS c FROM ( ".
                     "SELECT DISTINCT companies.company_id FROM companies ".
                     "JOIN company_hs4_conn ON companies.company_id=company_hs4_conn.company_id ".
                     "JOIN hs_codes ON hs_codes.hscode=company_hs4_conn.hs4_id ".
                     "WHERE company_hs4_conn.imp_exp=1 AND chapter='$hs2')x "
            ;
        } else if ($hs4 != "") {
            $query = "SELECT count(*) AS c FROM ( ".
                     "SELECT DISTINCT companies.company_id FROM companies ".
                     "JOIN company_hs4_conn ON companies.company_id=company_hs4_conn.company_id ".
                     "WHERE company_hs4_conn.imp_exp=1 AND (company_hs4_conn.hs4_id='$hs4' OR company_hs4_conn.hs4_id='$hs4 S'))x "
            ;
        } else {
            $query = "SELECT count(*) AS c FROM ( ".
                     "SELECT DISTINCT companies.company_id FROM companies ".
                     "JOIN company_hs4_conn ON companies.company_id=company_hs4_conn.company_id ".
                     "WHERE company_hs4_conn.imp_exp=1 AND (company_hs4_conn.hs4_id LIKE '" . $hs2 . "__' OR company_hs4_conn.hs4_id LIKE '" . $hs2 . "_' OR company_hs4_conn.hs4_id='$hs2'))x"
            ;
        }
        $res = Database::executeQuery($query);
        $data = Database::getDataFromResource($res);
        if ($data[0]['c'] == "0") {
            $query = "SELECT count(*) AS c FROM (".
                     "SELECT DISTINCT companies.company_id FROM companies ".
                     "JOIN company_hs4_conn ON companies.company_id=company_hs4_conn.company_id ".
                     "WHERE company_hs4_conn.imp_exp=1 AND company_hs4_conn.hs4_id='$hs2')x"
            ;
            $res = Database::executeQuery($query);
            $data = Database::getDataFromResource($res);
        }
        return intval($data[0]['c'], 10);
    }

    public function getBuyingLeadsLike($text) {
        $archiveDate = date("Y-m-d", time() - 14 * 24 * 60 * 60);
        $query = "SELECT businessleads.*, companies.companyname, companies.hphungarian, companies.country, companies.zipcode, companies.city, companies.street, companies.sttype, companies.houseno, companies.address, companies.phone1, companies.email1, companies.fax1, companies.contactperson1 FROM businessleads ".
                 "JOIN companies ON businessleads.company_id=companies.company_id ".
                 "WHERE buysell=1 AND postdate >= '$archiveDate' AND accepted_at IS NOT NULL";

/*
        $query = "SELECT * FROM companies ".
                 "JOIN businessleads ON businessleads.company_id=companies.company_id ".
                 "WHERE buysell=1 AND accepted_at >= '$archiveDate'"
        ;
*/
        $res = Database::executeQuery($query);
        $data = Database::getDatafromResource($res);
        return $data;
    }

    public function getSellingLeadsLike($text) {
        $archiveDate = date("Y-m-d", time() - 14 * 24 * 60 * 60);
        $query = "SELECT businessleads.*, companies.companyname, companies.hphungarian, companies.country, companies.zipcode, companies.city, companies.street, companies.sttype, companies.houseno, companies.address, companies.phone1, companies.email1, companies.fax1, companies.contactperson1 FROM companies ".
                 "JOIN businessleads ON businessleads.company_id=companies.company_id ".
                 "WHERE buysell=2 AND postdate >= '$archiveDate' AND accepted_at IS NOT NULL"
        ;
        $res = Database::executeQuery($query);
        $data = Database::getDatafromResource($res);
        return $data;
    }

    public function isAuthorized($operation, $subject = "") {
        return true;
//        if (Auth::isAdmin()) { return true; }
/*
        switch ($operation) {
            case "update" :
            case "delete" :
            case "addnew" : {
                if (Auth::canChangeDrivers()) { return true; }
            }
            case "list" : {
                if (Auth::canListDrivers()) { return true; }
            }
        }
        return false;
*/
    }

    public function modifyDatabaseInput($data) {
        $result = array();
        foreach ($data as $key=>$value) {
            if ($key == "import") { $result[$key] = convertCheckboxValueToBoolean($value); }
            else if ($key == "export") { $result[$key] = convertCheckboxValueToBoolean($value); }
            else if ($key == "want_gigabannerlink") { $result[$key] = convertYNValueToBoolean($value); }
            else if ($key == "want_videolink") { $result[$key] = convertYNValueToBoolean($value); }
            else if ($key == "want_photolink") { $result[$key] = convertYNValueToBoolean($value); }
            else if ($key == "want_smallbanner_export") { $result[$key] = convertYNValueToBoolean($value); }
            else if ($key == "want_smallbanner_import") { $result[$key] = convertYNValueToBoolean($value); }
            else if (in_array($key, $this->myFieldList())) { $result[$key] = $value; }
        }
        return $result;
    }
}

?>
